using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Reflection;
using System.Text;
using Microsoft.Practices.Unity;
using MiniStock.Database;
using MiniStock.Entities.Common;
using MiniStock.Entities.Common.Query;

namespace MiniStock.BusinessLogic.QueryLogic.Stock
{
    public class SqlQueryBuilder
    {
        private readonly EntityDbContext _context;

        private Dictionary<Type, JoinDescriptor> _joins;
        private string _tableName;

        public SqlQueryBuilder([Dependency] EntityDbContext context)
        {
            _context = context;
        }

        public Dictionary<Type, string> Joins { get; set; }

        public List<FilterElement> Filter { get; set; }

        public string Order { get; set; }

        public int? PageSize { get; set; }

        public int? PageIndex { get; set; }

        public Type EntityType { get; set; }

        public string ToSql()
        {
            if (String.IsNullOrEmpty(Order))
                Order = "Id";
            _tableName = GetTableNameForEntityType(EntityType);
            ParseJoins();

            var queryBuilder = new StringBuilder();
            queryBuilder.Append("SELECT ");
            
            if (PageSize != null && PageIndex != null && PageSize != 0)
            {
                queryBuilder.AppendFormat("TOP {0}", PageSize*(PageIndex + 1)+1);
            }

            queryBuilder.AppendFormat("{0}.*", _tableName);
            foreach(var j in _joins.Values)
            foreach (var f in j.FieldsToInclude)
                queryBuilder.AppendFormat(", {0}.{1} {2}", j.TableName, f.Key, f.Value);

            queryBuilder.AppendFormat("\nFROM {0}\n", _tableName);

            foreach (var join in _joins)
            {
                queryBuilder.AppendFormat("\t{0}\n", join.Value.JoinCriteria);
            }

            if (Filter != null && Filter.Count > 0)
            {
                queryBuilder.Append("WHERE ");
                bool b = false;
                Filter.ForEach(i =>
                                   {
                                       if (b) queryBuilder.Append(" AND ");
                                       queryBuilder.AppendFormat("({0})", i.ToSql());
                                   });
            }
            queryBuilder.AppendFormat("ORDER BY {0}\n", Order);

            return queryBuilder.ToString();
        }

        private void ParseJoins()
        {
            _joins = new Dictionary<Type, JoinDescriptor>();
            if (Joins != null)
            {
                foreach (var join in Joins)
                {
                    var joinedTableName = GetTableNameForEntityType(join.Key);
                    var criteria = GetJoinCriteriaForEntityType(EntityType, _tableName, join.Key, joinedTableName, join.Value); 
                    var descriptor = new JoinDescriptor()
                                                {
                                                    JoinCriteria = String.Format("LEFT JOIN {0} ON {1}",
                                                                                 joinedTableName,
                                                                                 criteria
                                                                                 ),
                                                    JoinName = join.Value ?? join.Key.Name,
                                                    TableName = joinedTableName,
                                                    FieldsToInclude = new Dictionary<string, string>(),
                                                };
                    foreach (var s in GetScalarFiedsForEntityType(join.Key))
                    {
                        descriptor.FieldsToInclude.Add(s, String.Format("{0}_{1}", descriptor.JoinName, s));
                    }
                    _joins[join.Key] = descriptor;
                }
            }
        }

        private string GetJoinCriteriaForEntityType(Type source, string sourcePrefix, Type target, string targetPrefix,
                                                    string value, bool reversed = false)
        {
            string sourceSet = GetTableNameForEntityType(source);

            string sourceKey =
                (from pi in source.GetProperties()
                 where pi.Name == "Id" || pi.GetCustomAttributes(typeof (KeyAttribute), true).Length > 0
                 select pi.Name).Single();

            string targetRef =
                (from pi in target.GetProperties()
                 from pa in pi.GetCustomAttributes(typeof (ForeignKeyAttribute), true)
                 where pa != null && (value == null || ((ForeignKeyAttribute) pa).Name == value)
                 select pi.Name).FirstOrDefault();

            if (targetRef != null)
                return String.Format("{0}.{1} = {2}.{3}", sourcePrefix, sourceKey, targetPrefix, targetRef);

            targetRef =
                (from pi in target.GetProperties()
                 where pi.Name == sourceSet + sourceKey
                 select pi.Name).FirstOrDefault();

            if (targetRef != null)
                return String.Format("{0}.{1} = {2}.{3}", sourcePrefix, sourceKey, targetPrefix, targetRef);

            if (!reversed)
            {
                return GetJoinCriteriaForEntityType(target, targetPrefix, source, sourcePrefix, value, true);
            }

            throw new ApplicationException();
        }

        private string GetTableNameForEntityType(Type source)
        {
            string setName =
                (from pi in _context.GetType().GetProperties()
                 where pi.PropertyType.IsGenericType
                       && pi.PropertyType.GetGenericArguments().FirstOrDefault() == source
                 select pi.Name).FirstOrDefault();
            return setName;
        }

        private List<string> GetScalarFiedsForEntityType(Type source)
        {
            var result = new List<string>();
            foreach (PropertyInfo pi in source.GetProperties())
            {
                var effectiveType = pi.PropertyType;
                if (effectiveType.IsGenericType)
                {
                    if (effectiveType == typeof (Nullable<>))
                        effectiveType = effectiveType.GetGenericArguments().Single();
                    else
                        continue;
                }
                
                if (typeof (EntityBase).IsAssignableFrom(effectiveType))
                    continue;

                if(!effectiveType.Namespace.StartsWith("System"))
                    continue;

                result.Add(pi.Name);
            }

            return result;
        }


        #region Nested type: JoinDescriptor

        private class JoinDescriptor
        {
            public string JoinName { get; set; }
            public string TableName { get; set; }
            public string JoinCriteria { get; set; }
            public Dictionary<string, string> FieldsToInclude { get; set; }
        }

        #endregion
    }
}